﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SEO.DAL
{
    /// <summary>
    /// 版权所有: 版权所有(C) 2011，上海云里网络科技有限公司
    /// 内容摘要: EDMSQLITEEmails
    /// 完成日期：2011年9月12日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMSQLITEEmails
    {
        /// <summary>
        /// 插入电子邮件
        /// </summary>
        /// <param name="strEmail">邮箱地址</param>
        /// <param name="iUserID">用户ID</param>
        /// <param name="iGroupID">分组ID</param>
        public static void InsertEMails(string strEmail,int iUserID,int iGroupID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "insert into Emails(mail_id,mail_email,user_id,group_id) " +
                    " values(" + GetAutoAddNum() + ",'" + strEmail + "'," + iUserID + "," + iGroupID + ")";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("插入电子邮件 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 删除电子邮件
        /// </summary>
        /// <param name="strMailID">电子邮件ID</param>
        public static void DeleteEmails(string strMailID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "delete from Emails where mail_id=" + strMailID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("删除电子邮件 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 删除所有电子邮件
        /// </summary>
        public static void DeleteAllEmails(string strGroupID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "delete from Emails where group_id=" + strGroupID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("删除所有电子邮件 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 更新电子邮件状态
        /// </summary>
        /// <param name="iMailID">Mail ID</param>
        public static void UpdateEmailStatus(int iMailID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update Emails set post_status=1 where mail_id=" + iMailID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新电子邮件状态 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 更新所有邮件未发送状态
        /// </summary>
        public static void UpdateEmailStatus()
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update Emails set post_status=0";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新所有邮件未发送状态 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 更新邮件地址状态
        /// </summary>
        /// <param name="strStatus">1为已发布，0为未发布</param>
        /// <param name="strMailID"></param>
        public static void UpdateEmailStatus(string strStatus,string strMailID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update Emails set post_status=" + strStatus + " where mail_id=" + strMailID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新邮件地址状态 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 更新邮件地址状态
        /// </summary>
        /// <param name="strStatus">1为已发布，0为未发布</param>
        public static void UpdateAllEmailStatus(string strStatus)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update Emails set post_status=" + strStatus;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新邮件地址状态 -> " + ex.Message);
            }
        }


        /// <summary>
        /// Emails获取最大ID
        /// </summary>
        /// <returns></returns>
        public static int GetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select mail_id from Emails order by mail_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["mail_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("Emails获取最大ID -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 检测电子邮件是否存在
        /// </summary>
        /// <param name="strEmail">电子邮件</param>
        /// <returns></returns>
        public static bool CheckMailEmailsIsExist(string strEmail,int iGroupID)
        {
            bool bValue = false;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select mail_id from Emails where mail_email='" + strEmail + "' and group_id=" + iGroupID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    bValue = true;
                }
                else
                {
                    bValue = false;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("检测电子邮件是否存在 -> " + ex.Message);
            }

            return bValue;
        }

        /// <summary>
        /// 统计Email数量
        /// </summary>
        /// <param name="iGroupID">分组ID</param>
        /// <returns></returns>
        public static int CountEmailNum(string strWhere)
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select count(mail_id) as num from Emails " + strWhere;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["num"].ToString());
                }
                else
                {
                    iNum = 0;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("统计Email数量 -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 绑定表EMails到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public static void BindEMailDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataAdapter da = new SQLiteDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "Emails");
                ds.Tables["Emails"].Columns.Add("PostStatus", typeof(string));
                for (int i = 0; i < ds.Tables["Emails"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["Emails"].Rows[i];
                    if (dr["post_status"].ToString() == "0")
                    {
                        dr["PostStatus"] = "未发布";
                    }
                    else
                    {
                        dr["PostStatus"] = "已发布";
                    }
                }
                dgv.DataSource = ds.Tables["Emails"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定Emails -> " + ex.Message);
            }
        }


        /// <summary>
        /// 导出邮件数据到Txt
        /// </summary>
        /// <param name="strStatus">0为未发布，1为发布</param>
        /// <returns></returns>
        public string ExportEDMMailsForTxt(string strWhere)
        {
            string strReturnValue = "";
            try
            {
                int k = 1;
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select mail_email from Emails "+ strWhere +" order by mail_id";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    string strMailContent = dr["mail_email"].ToString();
                    string strValue = k + "	" + strMailContent  + "\r\n";
                    strReturnValue += strValue;
                    k++;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("导出邮件数据到Txt -> " + ex.Message);
            }

            return strReturnValue;

        }
    }
}
